> - Rate Projections Functions Walkthrough
- Accruals and Cash
- Repeating Formulae
- Range Names and References
- Optional Parameters
- Using PmtsPerYear
- Modelling Seasonality
- Calculating Interest
- Using Business Functions in VBA (Visual Basic for Applications)
- Array Functions
- Volatility
- Annual Equivalent Rates
- Array Function
- Auto Multi Functions
- Variable Plurality
- GoalSeek
- Whereas Daycount can be specified with uneven length periods, and even take into account business days, PmtsPerYear is simply a figure specifying how many payments are made in a year assuming equal intervals of payment, that can be in advance or in arrear. This is in practise, accurate enough for a lot of analytical work.
- PmtsPerYear is used where the internal mathematics make use of annuity adjustment formulae such as CorrectionM.
- Computationally, using PmtsPerYear is much more direct than a full DayCount calculation, because the annuity formulae can use it directly. NPV functions that use daycount have to determine payment dates more laboriously, and typically have to iterate through a cash flow array to determine NPV. In other words, they do it the long way whereas PmtsPerYear facilitates using off-the-shelf annuity formulae.
- The NPV at 10% AER of a 10 year annuity of $10 p.a. payable annually in arrears is $61.45. You can do this in BF with PVM(10,10%,10,-1) with PmtsPerYear or even with Excel"s PV function (PV(10%,10,10)).
- If the payment changes to quarterly in arrear, the NPV at 10% AER is higher, at $63.70, because it is more onerous to pay quarterly rather than annually. Using a PmtsPerYear of -4 accomplished this (PVM(10,10%,10,-4)). You can achieve the same with Excel"s PV function if you make the payment 2.5, the number of periods 40 and the discount rate equal to (and keeping the AER the same) 1.10^0.025-1.0 = 2.41% per quarter (PV((1+10%)^0.025-1,40,2.5)).
- If the payment were quarterly in arrear on a calendar basis i.e. (1 April, 1 July etc) the PVM or PV functions are, on reflection, just approximations, because we don"t know that calendar quarter days are equally spaced throughout the year. In fact they are not.
- If the problem is compounded by saying that the annuity starts midway through such a period i.e. you get your first payment after 6 weeks or so, clearly the annuity formulae in PVM and PV are out of their depth. The assumption that has been broken is that the periods have become unequal and irregular.
- The answer to the last two parts of the problem, if accuracy is required, will not be found with PV or PVM. Instead you will either have to model the entire cash flow and use BF"s PVT, Excels XNPV functions, BF"s PVBF (2004, 40,(1.1^0.025-1)*4, 2.5, 0,3, {-1.01,-4.01,-7.01,-10.01}) or BF"s PVCon (PVCon(2004,0.1,2004,2014,10,6,{-1.01,-4.01,-7.01,-10.01},3,{-1.01,-4.01,-7.01,-10.01}).
- Sometimes, you don"t have a choice, and often PmtsPerYear is just easier to work with.
- For project appraisal, using PmtsPerYear is more than sufficient for establishing NPV"s etc. By taking account payment frequency at all you are already streets ahead of the plain old NPV function.
- For financial engineering, the daycount based functions are perhaps better because you will probably want 100% internal consistency between the timing of payments and the present value of principal amounts, and will perhaps want business day accuracy too. Daycount functions are slower because NPV is determined on a cash flow by cash flow basis, so you may not want sheets and sheets of them, but for NPV functions this is rarely the case.
- PmtsPerYear can have a value from -999 to +999, but typically might be 4 (for quarterly in advance) or -4 (for quarterly in arrear). A value of 999 (or any large number) would just imply continuous or daily payments.
Using PmtsPerYear | |
| Pro"s and Con"s of this short-cut approach to payment frequency | |
| PmtsPerYear (Payments Per Year) describes how payments are made when calculating Net Present Values. There is an obvious similarity with DayCount, Periods and CashBasis, which perform the same role in projections functions and some NPV functions in determining when cash moves. The difference is that PmtsPerYear is much narrower in its remit than the daycount variables as explained below. PmtsPerYear is used in the DCF Annual, Basic Interest Rate Conversions and Annuity families. It can be realised that even after the preceding explanation one can be very unclear over how PmtsPerYear and the DayCount variables are different. If you know the annuity formulae, consider this example: Which should you use, functions that use DayCount or PmtsPerYear? |